package com.wzy.scns.repositery;

import com.wzy.scns.entity.SelectCourse;
import com.wzy.scns.vo.SCStuInfo;
import com.wzy.scns.vo.StudentSelectMsgInterfaceVo;
import com.wzy.scns.vo.StudentSelectMsgVo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;


public interface SelectCourseRepository extends JpaRepository<SelectCourse, Integer> {

    @Transactional
    int deleteAllByStudentCode(String code);

    @Transactional
    int removeByCourseCodeAndStudentCode(String courseCode, String studentCode);

    SelectCourse findByStudentCodeAndCourseCode(String studentCode, String courseCode);

    int deleteByStudentCode(String studentCode);

    List<SelectCourse> findByStudentCode(String code);

    //原生
    //s.code as studentCode 最后确定的名字需要与接口get方法后面的名字匹配
    //如s.code as studentCode => getStudentCode()  s.code => getCode()
    @Query(value = "SELECT s.code AS studentCode,s.name AS studentName,\n" +
            "c.code AS courseCode,c.name AS courseName,\n" +
            "sc.achievement as achievement \n" +
            "FROM student as s,course as c,select_course as sc\n" +
            "WHERE \n" +
            "sc.student_code =?1 AND\n" +
            "s.code=sc.student_code AND c.code = sc.course_code", nativeQuery = true)
    //JPA 原生 sql 的返回不能直接转换成对象，原生 sql 的返回形式是 Map 、 List<Map> 或 Object，
    //因此可以使用 List<Map<String,Object>> 接收 或者用一个List<接口>,接口里只用写每个get属性的方法
    List<StudentSelectMsgInterfaceVo> selectMsgBySCode(String studentCode);


    //jpql 多表联查，有属性名相同时，自行创建接受结果类的构造函数，
    //按构造函数顺序输入从不同的表中取出来的值，含义要一一对应
    @Query(value = "select new com.wzy.scns.vo.StudentSelectMsgVo" +
            "(s.code,s.name,c.code,c.name,sc.achievement) " +
            "FROM SelectCourse as sc,Course as c, Student as s " +
            //from后第一个表单会被当成sort的表格
            //Student在前时，order by s.achievement
            //SelectCourse在前时，order by sc.achievement
            "WHERE sc.studentCode =?1 AND s.code=sc.studentCode AND c.code = sc.courseCode")
    Page<StudentSelectMsgVo> selectMsgBySCodeOrder(String studentCode, Pageable pageable);

    @Query(value = "select new com.wzy.scns.vo.SCStuInfo" +
            "(s.name,s.code,s.department,c.name,c.code,sc.selectDate,sc.achievement) " +
            "FROM Student as s,Course as c,  SelectCourse as sc " +
            "WHERE sc.courseCode =?1 AND s.code=sc.studentCode AND c.code = sc.courseCode")
    Page<SCStuInfo> getSCStuInfo(String courseCode, Pageable page);

    @Query(value = "select achievement " +
            "FROM SelectCourse  WHERE courseCode =?1")
    Float[] getMaxMinAvgByCCode(String courseCode);

    @Query("select max(achievement) as max,min(achievement) as min,avg(achievement) as avg  from SelectCourse where courseCode=?1")
    Map findMaxMinAvg(String code);
    List<SelectCourse> findByCourseCode(String code);

}
